package com.travel.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.travel.dao.ScenicDao;
import com.travel.entity.Scenic;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class ScenicDaoImpl implements ScenicDao{

	private DBUtils db = new DBUtils();
	
	@Override
	public List<Scenic> find() {
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic";
		ResultSet rs = db.query(sql);
		List<Scenic> list = new ArrayList<Scenic>();
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				Scenic scenic = new Scenic(scenicId, scenicName, scenicDesc, scenicPicture, scenicPrice);
				list.add(scenic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int insertScenic(Scenic scenic) {
		String sql = "insert into tab_scenic(scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price) values(?,?,?,?,?)";
		int insert = db.update(sql, scenic.getScenicId(),scenic.getScenicName(),scenic.getScenicDesc(),scenic.getScenicPicture(),scenic.getScenicPrice());
		return insert;
	}

	@Override
	public int updateScenic(Scenic scenic) {
		String sql = "update tab_scenic set scenic_name=?,scenic_desc=?,scenic_picture=?,scenic_price=? where scenic_id=?";
		int update = db.update(sql, scenic.getScenicName(),scenic.getScenicDesc(),scenic.getScenicPicture(),scenic.getScenicPrice(),scenic.getScenicId());
		return update;
	}

	@Override
	public int deleteId(int deleteId) {
		String sql = "delete from tab_scenic where scenic_id=?";
		int delete = db.update(sql, deleteId);
		return delete;
	}

	@Override
	public List<Scenic> findByScenicName(String scenicName) {
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic where scenic_name like concat('%',?,'%')";
		System.out.println(sql);
		ResultSet rs = db.query(sql, scenicName);
		List<Scenic> list = new ArrayList<Scenic>();
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				Scenic scenic = new Scenic(scenicId, scenicName, scenicDesc, scenicPicture, scenicPrice);
				list.add(scenic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return list;
	}

	@Override
	public PageInfo<Scenic> findByPage(int pageNum, int pageSize) {
	
		//获取分页结果的所有数据
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic limit ?,?";
		ResultSet rs = db.query(sql, (pageNum-1)*pageSize,pageSize);
		List<Scenic> list = new ArrayList<Scenic>();
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				Scenic scenic = new Scenic(scenicId, scenicName, scenicDesc, scenicPicture, scenicPrice);
				list.add(scenic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		
		//获取总记录数(未分页之前)
		int total = 0;
		String sql1 = "select count(*) from tab_scenic";
		rs = db.query(sql1);
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//构建分页对象
		PageInfo<Scenic> pageInfo = new PageInfo<Scenic>(list, pageNum, pageSize,total);
		return pageInfo;
	}

	@Override
	public PageInfo<Scenic> findByPage(int pageNum, int pageSize, String scenicName) {
		//获取分页结果的所有数据
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic where scenic_name like concat('%',?,'%') limit ?,?";
		ResultSet rs = db.query(sql,scenicName,(pageNum-1)*pageSize,pageSize);
		List<Scenic> list = new ArrayList<Scenic>();
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				String scenicName1 = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				Scenic scenic = new Scenic(scenicId, scenicName1, scenicDesc, scenicPicture, scenicPrice);
				list.add(scenic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		
		//获取总记录数(未分页之前)
		int total = 0;
		String sql1 = "select count(*) from tab_scenic where scenic_name like concat('%',?,'%')";
		rs = db.query(sql1,scenicName);
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//构建分页对象
		PageInfo<Scenic> pageInfo = new PageInfo<Scenic>(list, pageNum, pageSize,total);
		return pageInfo;
	}

	@Override
	public Scenic findByScenicId(int scenicId) {
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic where scenic_id=?";
		ResultSet rs = db.query(sql,scenicId);
		Scenic scenic = null;
		try {
			while (rs.next()) {
				//int scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				scenic = new Scenic(scenicId, scenicName, scenicDesc, scenicPicture, scenicPrice);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return scenic;
	}

	@Override
	public List<Scenic> getScenicByScenicName(String scenicName) {
		
		String sql = "select scenic_id,scenic_name,scenic_desc,scenic_picture,scenic_price from tab_scenic where scenic_name=?";
		ResultSet rs = db.query(sql, scenicName);
		List<Scenic> list = new ArrayList<Scenic>();
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture = rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");
				Scenic scenic = new Scenic(scenicId, scenicName, scenicDesc, scenicPicture, scenicPrice);
				list.add(scenic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return list;
	}
	
	@Override
	public List<Scenic> recommendScenic() {
		List<Scenic> list = new ArrayList<Scenic>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT S.scenic_id,S.scenic_name,S.scenic_desc,S.scenic_picture,S.scenic_price \r\n" + "from tab_scenic S\r\n"
				+ "JOIN tab_scenic_order O ON S.scenic_id=O.scenic_id \r\n"
				+ "GROUP BY O.scenic_id ORDER BY count(O.scenic_id) desc LIMIT 6");
		ResultSet rs = db.query(sb.toString());
		// 遍历结果集
		try {
			while (rs.next()) {
				int scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				String scenicDesc = rs.getString("scenic_desc");
				String scenicPicture=rs.getString("scenic_picture");
				BigDecimal scenicPrice = rs.getBigDecimal("scenic_price");

				// 构建实体模型
				Scenic scenic = new Scenic(scenicId, scenicName, scenicDesc,scenicPicture, scenicPrice);
				// 添加到集合中
				list.add(scenic);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
